package com.ywxx.dyhelper.utils;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.io.File;

/**
 * 数据库操作类
 * @author pqq
 */
public class SqliteDBAcces
{
    public String FilePath;
    public String FileName;
    private SQLiteDatabase mDatabase;

    public SqliteDBAcces(SQLiteDatabase database)
    {
        this.mDatabase = database;
        FilePath = database.getPath(); // 获取文件路径
        FileName = FilePath.substring(FilePath.lastIndexOf("/") + 1);// 获取文件名
    }

    /**
     * 安卓新特性
     * @param path
     * @return
     */
    public static boolean DeleteOldData(String path)
    {
        //安卓SQLITE3.7新特性 //.db-shm和.db-wal
        try
        {
            File db_shm = new File(path + "-shm");
            File db_wal = new File(path + "-wal");
            if (db_shm.exists() == true)
            {
                db_shm.delete();
            }
            if (db_wal.exists() == true)
            {
                db_wal.delete();
            }
        } catch (Exception e)
        {
            return false;
        }
        return true;
    }

    public SQLiteDatabase getDatabase()
    {
        return mDatabase;
    }

    /**
     * 插入语句
     * @param /Insertq
     * @return
     */
    public boolean Insert(String tbname, ContentValues cv)
    {
        if (mDatabase == null || mDatabase.isOpen() == false)
            return false;

        return mDatabase.insert(tbname, null, cv) > 0 ? true : false;
    }

    /**
     * 查询语句
     * @param cmdText
     * @return
     */
    public Cursor Query(String cmdText)
    {
        if (mDatabase == null || mDatabase.isOpen() == false)
            return null;
        try
        {
            return mDatabase.rawQuery(cmdText, null);
        } catch (SQLiteException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
        }
        return null;
    }

    /**
     * 查询语句
     * @param cmdText
     * @return
     */
    public Cursor Query(String cmdText, String[] selectionArgs)
    {
        if (mDatabase == null || mDatabase.isOpen() == false)
            return null;
        try
        {
            return mDatabase.rawQuery(cmdText, selectionArgs);
        } catch (SQLiteException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
        }
        return null;
    }

    /**
     * 查询语句
     * @param sql
     * @param selectionArgs
     * @return
     */
    public Cursor queryByWhere(String sql, String[] selectionArgs)
    {
        if (mDatabase == null || mDatabase.isOpen() == false)
            return null;
        try
        {
            return mDatabase.rawQuery(sql, selectionArgs);
        } catch (SQLiteException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
        }
        return null;
    }

    public boolean Delete(String table, String whereClause, String[] whereArgs)
    {
        int d = mDatabase.delete(table, whereClause, whereArgs);
        if (d > 0)
        {
            return true;
        } else
        {
            return false;
        }
    }

    /**
     * 更新语句
     * @param cmdText
     * @return
     */
    public boolean Execute(String cmdText)
    {
        try
        {
            mDatabase.execSQL(cmdText);
        } catch (SQLException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
            return false;
        }
        return true;
    }

    /**
     * 更新语句
     * @param cmdText
     * @return
     */
    public boolean Execute(String cmdText, Object[] bindargs)
    {
        try
        {
            mDatabase.execSQL(cmdText, bindargs);
        } catch (SQLException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
            return false;
        }
        return true;
    }

    public boolean replace(String table, String nullColumnHack, ContentValues initialValues)
    {
        try
        {
            mDatabase.replace(table, nullColumnHack, initialValues);
        } catch (SQLException e)
        {
            Log.e("SQLITE ERROR", e.getMessage());
            return false;
        }
        return true;

    }

    /**
     * 设置二进制
     * @param TableName
     * @param FieldName
     * @param strWhere
     * @return boolean
     */
    public boolean SetBinaryValue(String TableName, String FieldName, byte[] data, String strWhere)
    {
        ContentValues values = new ContentValues();
        values.put(FieldName, data);
        int iReturn = mDatabase.update(TableName, values, strWhere, null);
        if (iReturn > 0)
            return true;
        else
            return false;
    }

    /**
     * 设置二进制
     * @param TableName
     * @param values
     * @param strWhere
     * @return
     */
    public boolean SetBinaryValues(String TableName, ContentValues values, String strWhere)
    {
        int iReturn = mDatabase.update(TableName, values, strWhere, null);
        if (iReturn > 0)
            return true;
        else
            return false;
    }

    public void close()
    {
        if (mDatabase == null || mDatabase.isOpen() == false)
            return;
        mDatabase.close();
    }

    public JSONArray QueryByJSON(String strSQLText)
    {

        JSONArray list = new JSONArray();
        SQLiteCursor cursor = (SQLiteCursor) Query(strSQLText);
        if (cursor != null && cursor.getCount() > 0)
        {
            try
            {
                //索引，加速获取数据
                String[] names = cursor.getColumnNames();//字段
                while (cursor.moveToNext())
                {
                    JSONObject obj = new JSONObject();
                    for (int n = 0; n < cursor.getColumnNames().length; n++)
                    {
                        String field = names[n];
                        switch (cursor.getType(n))
                        {
                            case Cursor.FIELD_TYPE_STRING:
                                obj.put(field, cursor.getString(n));
                                break;
                            case Cursor.FIELD_TYPE_INTEGER:
                                obj.put(field, cursor.getInt(n));
                                break;
                            case Cursor.FIELD_TYPE_FLOAT:
                                obj.put(field, cursor.getFloat(n));
                                break;
                            case Cursor.FIELD_TYPE_BLOB:
                                obj.put(field, cursor.getBlob(n));
                                break;
                        }

                    }
                    list.add(obj);
                }
            } catch (Exception e)
            {
                Log.e("SQLITE2JSON", e.getMessage());
            }
            cursor.close();
        }
        return list;
    }

    /**
     * 删除表
     * @param tableName
     * @return
     */
    public boolean DropTable(String tableName)
    {
        // 取得所有表
        String strSQLText = String.format("DROP TABLE %s ", tableName);
        return Execute(strSQLText);
    }

    /**
     * 创建自动发送广告的存储
     * @return
     */
    public boolean CreateAutoAdv()
    {
        StringBuffer str = new StringBuffer();
        str.append("create table if not exists hy_auto_adv_log");
        str.append("(id INTEGER PRIMARY KEY AUTOINCREMENT,");
        str.append("guid varchar(40),");
        str.append("reviceman varchar(255),");
        str.append("sendtype varchar(20),");
        str.append("sendmsg varchar(255),");
        str.append("createdate varchar(100))");
        return Execute(str.toString());
    }
}
